﻿ALTER PROCEDURE sp_CPA_GPA(@studentId VARCHAR(5))
AS
DECLARE @totalCredits AS NUMERIC(3, 0);
DECLARE @GPA AS FLOAT;

SELECT year, semester, SUM(credits * mark) / SUM(credits) AS GPA, 
	(
		SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))
	) AS CPA,
	(
	CASE
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) < 1.0
		THEN 'Kém'
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 1.0 AND 1.99
		THEN 'Yếu'
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 2.0 AND 2.49
		THEN 'Trung bình'
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 2.5 AND 3.19
		THEN 'Khá'
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 3.2 AND 3.59
		THEN 'Giỏi'
		WHEN
		(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) >= 3.6
		THEN 'Xuất sắc'
	END
	) AS XepLoai,
	(
		SELECT SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
		WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))
	) AS TotalCredits,
	(
	CASE 
		WHEN
			(SELECT SUM(credits)
			FROM course
				JOIN
				(SELECT * FROM takes AS T
				WHERE
					((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
					T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
					T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
				ON course.course_id = TT.course_id
				JOIN mapping_mark ON TT.grade = mapping_mark.grade
			WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) < 32
		THEN 1
		WHEN
			(SELECT SUM(credits)
			FROM course
				JOIN
				(SELECT * FROM takes AS T
				WHERE
					((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
					T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
					T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
				ON course.course_id = TT.course_id
				JOIN mapping_mark ON TT.grade = mapping_mark.grade
			WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 32 AND 63
		THEN 2
		WHEN
			(SELECT SUM(credits)
			FROM course
				JOIN
				(SELECT * FROM takes AS T
				WHERE
					((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
					T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
					T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
				ON course.course_id = TT.course_id
				JOIN mapping_mark ON TT.grade = mapping_mark.grade
			WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 64 AND 95
		THEN 3
		WHEN
			(SELECT SUM(credits)
			FROM course
				JOIN
				(SELECT * FROM takes AS T
				WHERE
					((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
					T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
					T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
				ON course.course_id = TT.course_id
				JOIN mapping_mark ON TT.grade = mapping_mark.grade
			WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) BETWEEN 66 AND 127
		THEN 4
		WHEN
			(SELECT SUM(credits)
			FROM course
				JOIN
				(SELECT * FROM takes AS T
				WHERE
					((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
					T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
					T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
				ON course.course_id = TT.course_id
				JOIN mapping_mark ON TT.grade = mapping_mark.grade
			WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) > 128
		THEN 5
	END) AS NamThu
	
FROM (SELECT * FROM takes
	WHERE ID=@studentId) AS T1 JOIN mapping_mark ON T1.grade = mapping_mark.grade JOIN course ON T1.course_id = course.course_id
GROUP BY year, semester
ORDER BY year, semester;